<?php
/* Inserts the XML array $arrXML to
 * the database with connection $con
 */
function insertWeatherXmlToDatabase($arrXml, $con) {
	prepareWeatherXml($arrXml);
	if ($result = 
            $con->query("SELECT `sol` FROM `weather` WHERE `sol` = \"".
                mysqli_real_escape_string($con, $arrXml['sol']). 
                "\" LIMIT 5")) 
    {
        printf("<br>Select returned %d rows.\n", $result->num_rows);
        
        if ($result->num_rows > 0) 
        {
            printf("<br>The sol <b>".$arrXml['sol']."</b> exists!!!");
            /* free result set */
            $result->close();
        }
        else 
        {
            /* free result set */
            $result->close();
            //insert the data in SQL
            $insertion = 
                $con->query("INSERT INTO weather VALUES(".
                    mysqli_real_escape_string($con, $arrXml['sol']).", \"" 
                    . mysqli_real_escape_string($con, $arrXml['terrestrial_date']) ."\", "
                    . mysqli_real_escape_string($con, $arrXml['magnitudes']['min_temp']). ", " 
                    . mysqli_real_escape_string($con, $arrXml['magnitudes']['max_temp']) .", "
					. mysqli_real_escape_string($con, $arrXml['magnitudes']['pressure']) .", \""
					. mysqli_real_escape_string($con, $arrXml['magnitudes']['pressure_string'])."\"".", \""
					. mysqli_real_escape_string($con, $arrXml['magnitudes']['abs_humidity'])."\"" .", "
					. mysqli_real_escape_string($con, $arrXml['magnitudes']['wind_speed']).", \""
					. mysqli_real_escape_string($con, $arrXml['magnitudes']['wind_direction'])."\"" .", \""
					. mysqli_real_escape_string($con, $arrXml['magnitudes']['atmo_opacity'])."\"" .", \""
					. mysqli_real_escape_string($con, $arrXml['magnitudes']['season']) ."\"".", "
					. mysqli_real_escape_string($con, $arrXml['magnitudes']['ls']) .", \""
					. mysqli_real_escape_string($con, $arrXml['magnitudes']['sunrise']) ."\"".", \""
					. mysqli_real_escape_string($con, $arrXml['magnitudes']['sunset'])."\""
					.");");
            if ($insertion === TRUE) 
			{
                printf("<br>Row added");
            }
            else
			{
                printf("<br>".$con->error);
            }
        }
    }
	else
	{
		printf("<br>".$con->error);
	}
}

function prepareWeatherXml($arrXml)
{
	// Date - Y-m-d (example: 2013-02-25)
	$arrXml['terrestrial_date'] = date("Y-m-d", strtotime($arrXml['terrestrial_date']));
	// Time - H:i:s (example: 06:00:00) (the instrument returns only whole hours for sunrise and sunset)
	$arrXml['magnitudes']['sunrise'] = date("H:i:s", strtotime(str_ireplace(" am", "", $arrXml['magnitudes']['sunrise'].":00:00")));
	$arrXml['magnitudes']['sunset'] = date("H:i:s", strtotime(intval(str_ireplace(" pm", "", $arrXml["magnitudes"]['sunset'])) + 12 .":00:00"));
	return $arrXml;
}

/*
 * Print the data from the database
 */
function ReadWeatherReport($con, $sol="*") 
{
    //report
    $report = array();

    //preventing SQL injection
    $sol = mysqli_real_escape_string($con, $sol);
    if($sol === "*") 
    {
        $extractDataQuery = "SELECT * FROM `weather`;";
    }
    else 
    {
        $extractDataQuery = "SELECT * FROM `weather` WHERE `sol` = \"".$sol."\";";
    }
    
    if ($result = $con->query($extractDataQuery)) 
    {
        if ($result->num_rows > 0) {
            $row = mysqli_fetch_assoc($result);
            //$report['data'] = $row;
            $report = array("data" => $row);
            //array_push($report['data'], $row);
        }  
        //if we got no report we should offer options for the closest reports date
        else {
            $closestReport = GetClosestReport($con, $sol);
            $report['alternative'] = $closestReport;
        }
          
          //free extractDataQuery set
          $result->Close();
    }
    else
	{
        printf($con->error);
    }
    
    return $report;
}

function GetClosestReport($con, $sol) {
    
    $report = array();

    $getLowerReportQuery = "SELECT `terrestrial_date` FROM `weather` WHERE `sol` < " . $sol . " ORDER BY sol DESC LIMIT 1";
    $getUpperReportQuery = "SELECT `terrestrial_date` FROM `weather` WHERE `sol` > " . $sol . " ORDER BY sol ASC LIMIT 1";
    $getRequestedDateQuery = "SELECT `terrestrial_date` FROM `weather` WHERE `sol` = " . $sol." LIMIT 1";

    $report['lower'] = RequestClosestSol($con, $getLowerReportQuery);
    $report['upper'] = RequestClosestSol($con, $getUpperReportQuery);

    return $report;
}

function RequestClosestSol($con, $query){

    if ($result = $con->query($query)) 
    {
        $row = mysqli_fetch_row($result);
        //free $result resource
        $result->Close();
        return $row[0];
    }
    else
    {
        printf($con->error);
    }    

}

function GetLastWeatherReport($con) {
    $report = array();

    $extractDataQuery = "SELECT * FROM  `weather` ORDER BY `sol` DESC LIMIT 1";
     if ($result = $con->query($extractDataQuery)) 
    {
        $row = mysqli_fetch_array($result);
        $report['data'] = $row;
        return $report;
    }
    else
    {
        printf($con->error);
    }
}

?>